package com.tencent.api.preparedstatement;

import org.junit.Test;

import java.sql.*;
import java.util.*;

public class PSCURDPart {
    @Test
    public void testInsert() throws ClassNotFoundException, SQLException {
        //注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/atguigu", "root", "1234");
        //编写SQL语句，动态值的部分使用？代替
        String sql ="INSERT INTO t_user(account, PASSWORD, nickname) values (?,?,?);";
        //创建preparedStatement，并且传入SQL语句结果
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        //占位符赋值
        preparedStatement.setObject(1,"test");
        preparedStatement.setObject(2,"test");
        preparedStatement.setObject(3,"二狗砸");

        //发送SQL语句，并接收
        int rows = preparedStatement.executeUpdate();
        //解析结果
        if(rows>0){
            System.out.println("数据插入成功！");
        }else {
            System.out.println("数据插入失败！");
        }
        //释放资源
        preparedStatement.close();
        connection.close();
    }
    @Test
    public void testUpdate() throws ClassNotFoundException, SQLException {
        //update t_user set nickname='三狗子' where id = 3;

        //注册驱动
        Class.forName("com.mysql.cj.jdbc.Driver");
        //获取连接
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "1234");
        //编写SQL语句，动态值的部分使用？代替
        String sql ="update t_user set nickname=? where account = ?";
        //创建preparedStatement，并且传入SQL语句结果
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        //占位符赋值
        preparedStatement.setObject(1,"wangjiajia");
        preparedStatement.setObject(2,"test");
        //发送SQL语句，并接收
        int rows = preparedStatement.executeUpdate();
        //解析结果
        if (rows>0){
            System.out.println("更新成功！");
        }else {
            System.out.println("更新失败！");
        }
        //释放资源
        preparedStatement.close();
        connection.close();

    }
    @Test
    public void testDelete() throws ClassNotFoundException, SQLException {
        //delete from t_user where account='test';
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "1234");
        String sql = "delete from t_user where account=?;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setObject(1,"test");
        int rows = preparedStatement.executeUpdate();
        if (rows>0)
            System.out.println("删除成功！");
        else
            System.out.println("删除失败！");
        preparedStatement.close();
        connection.close();
    }


    /**
     * 目标：查询所用用户数据，并且封装到一个Lis<Map> list集合中
     *
     * 数据库 -> resultSet -> java -> 一行 - map(key=列名，value=列的内容) ->  Lis<Map> list
     *
     * 实现思路：
     *      遍历行数据，一行对应一个map，获取一行的列名和对应的列的属性，装配即可
     *      将map装到一个集合就可以了
     *
     * 难点：
     *      如何获取列的名称？
     *
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void testSelect() throws ClassNotFoundException, SQLException {
        //select id,account,PASSWORD,nickname from t_user;
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "1234");
        String sql = "select id,account,PASSWORD,nickname from t_user";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
//        preparedStatement.setObject(1,"test");
        ResultSet resultSet = preparedStatement.executeQuery();
//        while (resultSet.next()){
//            String account = resultSet.getString("account");
//            String password = resultSet.getString("password");
//            String nickname = resultSet.getString("nickname");
//            System.out.println(account+password+nickname);
//        }

        List<Map> list = new ArrayList<>();

        //获取列的信息对象
        //metaData装的就是当前结果集列的信息对象（可以获取列的名称根据下标，可以获取列的数量
        ResultSetMetaData metaData = resultSet.getMetaData();

//        获取列的数量,有了它以后可以水平遍历列
        int columnCount = metaData.getColumnCount();

        while (resultSet.next()){
            Map map = new HashMap();
            //一行数据对应一个map

//            //纯手动取值
//            map.put("id",resultSet.getString("id"));
//            map.put("account",resultSet.getString("account"));
//            map.put("password",resultSet.getString("password"));
//            map.put("nickname",resultSet.getString("nickname"));

            //自动水平遍历列，从1开始，因为数据库中下标从1开始；并且小于等于总列数
            for (int i = 1; i <= columnCount; i++) {
                //获取指定列下标的值
                Object value = resultSet.getObject(i);
                //获取指定列下标的列的名称
                //getColumnLabel：会优先获取列的别名，没有别名选名称； 不要使用getColumnName：只会获取列名
                String columnLabel = metaData.getColumnLabel(i);
                map.put(columnLabel,value);
            }


            //一行数据中的所有列全部存到了map中
            //将map存储到集合中即可
            list.add(map);

        }
        System.out.println(list);
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
    //自己复现一遍
    @Test
    public void testSelectOK() throws ClassNotFoundException, SQLException {
        Class.forName("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection("jdbc:mysql:///atguigu", "root", "1234");
        String sql = "select * from t_user;";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        ResultSet resultSet = preparedStatement.executeQuery();
        List<Map> list = new ArrayList<>();
        ResultSetMetaData metaData = resultSet.getMetaData();
        int columnCount = metaData.getColumnCount();
        while (resultSet.next()){
            Map map = new HashMap();
            for (int i = 1; i <= columnCount; i++) {
                Object object = resultSet.getObject(i);
                String columnLabel = metaData.getColumnLabel(i);
                map.put(columnLabel,object);

            }
            list.add(map);
        }
        System.out.println(list);
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }
}
